import pandas as pd
from datetime import datetime
import os
from collections import Counter
import jieba
from config import Config

class ExcelGenerator:
    # Excel文件生成类
    
    def __init__(self, danmu_data):
        self.danmu_data = danmu_data
        self.writer = None
    
    def generate_excel_report(self, filename=None):
        # 生成完整的Excel报告
        if not self.danmu_data:
            print("没有弹幕数据，无法生成Excel报告")
            return False
        
        Config.create_output_dir()
        
        if filename is None:
            timestamp = datetime.now().strftime("%Y%m%d_%H%M%S")
            filename = f"{Config.OUTPUT_DIR}/bilibili_top300_report_{timestamp}.xlsx"
        else:
            filename = f"{Config.OUTPUT_DIR}/{filename}"
        
        try:
            with pd.ExcelWriter(filename, engine='openpyxl') as self.writer:
                self._generate_summary_sheet()
                self._generate_danmu_detail_sheet()
                self._generate_video_stats_sheet()
                self._generate_word_frequency_sheet()
                self._generate_analysis_sheet()
            
            print(f"Excel报告生成成功: {filename}")
            print(f"文件大小: {os.path.getsize(filename) / 1024 / 1024:.2f} MB")
            return True
            
        except Exception as e:
            print(f"生成Excel报告失败: {e}")
            import traceback
            traceback.print_exc()
            return False
    
    def _generate_summary_sheet(self):
        # 生成汇总表
        print("生成汇总表...")
        
        summary_data = {
            '统计项目': [
                '采集时间',
                '目标视频数',
                '成功获取视频数', 
                '总弹幕数量',
                '平均每个视频弹幕数',
                '数据来源',
                '备注'
            ],
            '数值': [
                datetime.now().strftime("%Y-%m-%d %H:%M:%S"),
                Config.TARGET_VIDEOS,
                len(set([item['bvid'] for item in self.danmu_data])),
                len(self.danmu_data),
                f"{len(self.danmu_data) / len(set([item['bvid'] for item in self.danmu_data])):.2f}" if self.danmu_data else "0",
                'B站综合排序前300视频',
                '自动生成的弹幕分析报告'
            ]
        }
        
        df_summary = pd.DataFrame(summary_data)
        df_summary.to_excel(self.writer, sheet_name='报告摘要', index=False)
        
        # 调整列宽
        worksheet = self.writer.sheets['报告摘要']
        worksheet.column_dimensions['A'].width = 20
        worksheet.column_dimensions['B'].width = 30
    
    def _generate_danmu_detail_sheet(self):
        # 生成弹幕明细表
        print("生成弹幕明细表...")
        
        detail_data = []
        for item in self.danmu_data:
            detail_data.append({
                '视频序号': item['video_index'],
                'BV号': item['bvid'],
                '视频链接': item['video_url'],
                '弹幕内容': item['danmu'],
                '弹幕长度': len(item['danmu']),
                '采集时间': datetime.fromtimestamp(item['timestamp']).strftime("%Y-%m-%d %H:%M:%S")
            })
        
        df_detail = pd.DataFrame(detail_data)
        df_detail.to_excel(self.writer, sheet_name='弹幕明细', index=False)
        
        # 调整列宽
        worksheet = self.writer.sheets['弹幕明细']
        worksheet.column_dimensions['A'].width = 10
        worksheet.column_dimensions['B'].width = 15
        worksheet.column_dimensions['C'].width = 40
        worksheet.column_dimensions['D'].width = 50
        worksheet.column_dimensions['E'].width = 10
        worksheet.column_dimensions['F'].width = 20
    
    def _generate_video_stats_sheet(self):
        # 生成视频统计表
        print("生成视频统计表...")
        
        # 按视频统计弹幕
        video_stats = {}
        for item in self.danmu_data:
            bvid = item['bvid']
            if bvid not in video_stats:
                video_stats[bvid] = {
                    'video_url': item['video_url'],
                    'video_index': item['video_index'],
                    'danmu_count': 0,
                    'danmu_list': []
                }
            video_stats[bvid]['danmu_count'] += 1
            video_stats[bvid]['danmu_list'].append(item['danmu'])
        
        stats_data = []
        for bvid, stats in video_stats.items():
            # 计算平均弹幕长度
            avg_length = sum(len(danmu) for danmu in stats['danmu_list']) / len(stats['danmu_list'])
            
            stats_data.append({
                '视频序号': stats['video_index'],
                'BV号': bvid,
                '视频链接': stats['video_url'],
                '弹幕数量': stats['danmu_count'],
                '平均弹幕长度': f"{avg_length:.2f}",
                '数据完整性': '完整' if stats['danmu_count'] > 0 else '不完整'
            })
        
        # 按弹幕数量排序
        stats_data.sort(key=lambda x: x['弹幕数量'], reverse=True)
        
        df_stats = pd.DataFrame(stats_data)
        df_stats.to_excel(self.writer, sheet_name='视频统计', index=False)
        
        # 调整列宽
        worksheet = self.writer.sheets['视频统计']
        worksheet.column_dimensions['A'].width = 10
        worksheet.column_dimensions['B'].width = 15
        worksheet.column_dimensions['C'].width = 40
        worksheet.column_dimensions['D'].width = 12
        worksheet.column_dimensions['E'].width = 15
        worksheet.column_dimensions['F'].width = 12
    
    def _generate_word_frequency_sheet(self):
        # 生成词频统计表
        print("生成词频统计表...")
        
        # 合并所有弹幕文本
        all_danmu = " ".join([item['danmu'] for item in self.danmu_data])
        
        if not all_danmu.strip():
            print("弹幕文本为空，跳过词频统计")
            return
        
        # 使用jieba分词
        words = jieba.lcut(all_danmu)
        
        # 过滤停用词和单个字符
        filtered_words = [word for word in words if len(word) > 1 and word not in Config.STOP_WORDS]
        
        # 词频统计
        word_freq = Counter(filtered_words)
        
        # 转换为DataFrame
        freq_data = []
        for word, freq in word_freq.most_common(100):  # 取前100个高频词
            freq_data.append({
                '词语': word,
                '出现次数': freq,
                '频率(%)': f"{(freq / len(filtered_words) * 100):.2f}%"
            })
        
        df_freq = pd.DataFrame(freq_data)
        df_freq.to_excel(self.writer, sheet_name='词频统计', index=False)
        
        # 调整列宽
        worksheet = self.writer.sheets['词频统计']
        worksheet.column_dimensions['A'].width = 20
        worksheet.column_dimensions['B'].width = 12
        worksheet.column_dimensions['C'].width = 12
    
    def _generate_analysis_sheet(self):
        # 生成分析报告表
        print("生成分析报告表...")
        
        if not self.danmu_data:
            return
        
        df = pd.DataFrame(self.danmu_data)
        
        # 基础统计
        total_videos = df['bvid'].nunique()
        total_danmu = len(df)
        avg_danmu_per_video = total_danmu / total_videos if total_videos > 0 else 0
        
        # 弹幕长度分析
        df['danmu_length'] = df['danmu'].str.len()
        avg_length = df['danmu_length'].mean()
        max_length = df['danmu_length'].max()
        min_length = df['danmu_length'].min()
        
        # 热门弹幕
        top_danmu = df['danmu'].value_counts().head(10)
        
        analysis_data = {
            '分析维度': [
                '数据概况',
                '数据概况',
                '数据概况',
                '弹幕长度分析',
                '弹幕长度分析', 
                '弹幕长度分析',
                '热门弹幕排名',
                '热门弹幕排名',
                '热门弹幕排名',
                '热门弹幕排名',
                '热门弹幕排名'
            ],
            '具体指标': [
                '成功获取视频数',
                '总弹幕数量',
                '平均每个视频弹幕数',
                '平均弹幕长度',
                '最长弹幕长度',
                '最短弹幕长度',
                '第1名',
                '第2名',
                '第3名',
                '第4名',
                '第5名'
            ],
            '数值/内容': [
                total_videos,
                total_danmu,
                f"{avg_danmu_per_video:.2f}",
                f"{avg_length:.2f} 字符",
                f"{max_length} 字符",
                f"{min_length} 字符"
            ]
        }
        
        # 添加热门弹幕
        for i, (danmu, count) in enumerate(top_danmu.items()):
            if i < 5:  # 只取前5名
                if i == 0:
                    analysis_data['数值/内容'].append(f"{danmu} (出现{count}次)")
                else:
                    analysis_data['数值/内容'].append(f"{danmu} (出现{count}次)")
        
        df_analysis = pd.DataFrame(analysis_data)
        df_analysis.to_excel(self.writer, sheet_name='分析报告', index=False)
        
        # 调整列宽
        worksheet = self.writer.sheets['分析报告']
        worksheet.column_dimensions['A'].width = 15
        worksheet.column_dimensions['B'].width = 20
        worksheet.column_dimensions['C'].width = 50